﻿// ====================================================================================================================
// Project: Camstar InSiteExtractor
// Source File: InSiteExtractor.cs
// Author: Roop Sin LE
// Created(MM/DD/YYYY HH:MM): 08/13/2010 9:14:21 PM
// Copyright: Camstar Inc.
// ====================================================================================================================

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.IO;
using Camstar.Utils.csv;
using log4net;
using InSiteExtractor.Definition;

namespace InSiteExtractor.Data
{
    /// <summary>
    /// Class to extract data and update value into Excel Loader
    /// </summary>
    internal class SqlDataExtractor
    {
        // Define a static logger variable so that it references the
        private static readonly ILog Log = LogManager.GetLogger(typeof (Program));

        // Retrieve a connection string by specifying the providerName.
        // Assumes one connection string per provider in the config file.
        private static string GetConnectionStringByProvider(string providerName)
        {
            // Return null on failure.
            string returnValue = null;

            // Get the collection of connection strings.
            ConnectionStringSettingsCollection settings =
                ConfigurationManager.ConnectionStrings;

            // Walk through the collection and return the first 
            // connection string matching the providerName.
            if (settings != null)
            {
                foreach (ConnectionStringSettings cs in settings)
                {
                    if (cs.ProviderName == providerName)
                        returnValue = cs.ConnectionString;
                    break;
                }
            }
            return returnValue;
        }

        /// <summary>
        /// Execute a Query to extract data from Database
        /// </summary>
        /// <param name="queryString">Indicate a Query</param>
        /// <param name="writer">Indicate outstream to write</param>
        static public DataTable ExecuteReader(string queryString)
        {
            Log.DebugFormat("Processing the SQL Query: {0}", queryString);
            // The connection string assumes that the Access 
            // Northwind.mdb is located in the c:\Data folder.
            DataTable table = new DataTable();
            string connectionString = GetConnectionStringByProvider(ExtractorConst.OracleClient);

            using (var connection =
                new OracleConnection(connectionString))
            {
                OracleCommand command = connection.CreateCommand();
                command.CommandText = queryString;

                try
                {
                    connection.Open();
                    OracleDataReader reader = command.ExecuteReader();
                    table.TableName = reader.GetSchemaTable().TableName;
                    int r = 0;
                    while (reader.Read())
                    {
                        
                        DataRow dataRow = table.NewRow();
                        for (int i = 0; i <= reader.FieldCount - 1; i++)
                        {
                            object obj = reader.GetValue(i);
                            if (!table.Columns.Contains(reader.GetName(i))) table.Columns.Add(reader.GetName(i));
                            dataRow[reader.GetName(i)] = reader.GetValue(i).ToString();
                        }
                        table.Rows.Add(dataRow);
                        // Update the status to the column 2
                        r++;
                        
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Log.Error(ex);
                }
            }

            return table;
        }

        /// <summary>
        /// Execute a Query to extract data from Database
        /// </summary>
        /// <param name="queryString">Indicate a Query</param>
        /// <param name="excelSheetUpdator">Indicate the Excel Object to used for updating cell values</param>
        public static int ExecuteReader(string queryString, ExcelWorkbookUpdator excelSheetUpdator)
        {
            Log.DebugFormat("Processing the SQL Query: {0}", queryString);
            int r = 0;
            // The connection string assumes that the Access 
            // Northwind.mdb is located in the c:\Data folder.
            string connectionString = GetConnectionStringByProvider(ExtractorConst.OracleClient);

            using (var connection =
                new OracleConnection(connectionString))
            {
                OracleCommand command = connection.CreateCommand();
                command.CommandText = queryString;

                try
                {
                    connection.Open();
                    OracleDataReader reader = command.ExecuteReader();
                    
                    while (reader.Read())
                    {
                        for (int i = 0; i <= reader.FieldCount - 1; i++)
                        {
                            object obj = reader.GetValue(i);
                            excelSheetUpdator.UpdateCell(r, reader.GetName(i), obj.ToString());
                            InSiteDataExtractorManager.Instance().Monitor.UpdateStatus(r, reader.GetName(i));
                        }
                        // Update the status to the column 1 & 2
                        excelSheetUpdator.UpdateCell(r, ExtractorConst.ExecuteColumOffset, ExtractorConst.ExecuteAction);
                        excelSheetUpdator.UpdateCell(r, ExtractorConst.CompMsgColumOffset, string.Format("Updated by InSiteExtractor on {0}", DateTime.Now.ToString()));
                        r++;
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                    Log.Error(ex);
                }
            }

            return r;
        }

        static public string GetTableName(string cdoName)
        {
            string tableName = "";
            string queryString =
                "SELECT DBTABLEDEFINITION.DBTABLENAME FROM CDODEFINITION , DBTABLEDEFINITION WHERE CDODEFINITION.DEFAULTTABLEID = DBTABLEDEFINITION.DBTABLEID AND UPPER(CDONAME) = UPPER(:CDONAME)";

            string connectionString = GetConnectionStringByProvider(ExtractorConst.OracleClient);

            using (var connection =
                new OracleConnection(connectionString))
            {
                OracleCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                command.Parameters.AddWithValue(ExtractorConst.CdoName, cdoName);

                try
                {
                    connection.Open();
                    object dbTableName = command.ExecuteScalar();

                    if (dbTableName == null)
                    {
                        Log.WarnFormat("The CDO {0} cannot be extraced DB Table Name. Please verify it!", cdoName);
                        tableName = cdoName;
                    }
                    else
                    {
                        tableName = dbTableName.ToString();
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }

                return tableName.ToUpper();
            }
        }


        static public Dictionary<string , string > GetDbFieldInfo(string cdoName, string fieldName)
        {
            Dictionary<string , string > dbFieldInfo = new Dictionary<string, string>();
            string queryString =
                "SELECT CDODEFINITION.CDONAME, DBTABLEDEFINITION.DBTABLENAME, CDOFIELDS.FIELDNAME, FIELDDEFINITIONS.FIELDDEFNAME  , DBCOLUMNS.COLUMNNAME, DBFIELDTABLEDEFINITION.DBTABLENAME FIELDDBTABLENAME ";
            queryString +=
                " FROM CDODEFINITION , DBTABLEDEFINITION, CDOFIELDS , FIELDDEFINITIONS , DBCOLUMNS , CDODEFINITION CDOFIELDDEFINITION , DBTABLEDEFINITION DBFIELDTABLEDEFINITION ";
            queryString +=
                " WHERE CDODEFINITION.DEFAULTTABLEID = DBTABLEDEFINITION.DBTABLEID AND CDOFIELDS.DBTABLEID = DBTABLEDEFINITION.DBTABLEID ";
            queryString += " AND DBTABLEDEFINITION.DBTABLEID = DBCOLUMNS.DBTABLEID ";
            queryString += " AND CDOFIELDS.FIELDDEFID = FIELDDEFINITIONS.FIELDDEFID  ";
            queryString += " AND CDOFIELDS.DBCOLUMNID = DBCOLUMNS.DBCOLUMNID  ";
            queryString += " AND CDOFIELDDEFINITION.CDONAME = FIELDDEFINITIONS.FIELDDEFNAME ";
            queryString += " AND CDOFIELDDEFINITION.DEFAULTTABLEID = DBFIELDTABLEDEFINITION.DBTABLEID ";
            queryString += " AND UPPER(CDODEFINITION.CDONAME) = UPPER(:CDONAME) AND UPPER(CDOFIELDS.FIELDNAME) = UPPER(:CDOFIELD) ";

            string connectionString = GetConnectionStringByProvider(ExtractorConst.OracleClient);

            using (var connection =
                new OracleConnection(connectionString))
            {
                OracleCommand command = connection.CreateCommand();
                command.CommandText = queryString;
                command.Parameters.AddWithValue(ExtractorConst.CdoName, cdoName);
                command.Parameters.AddWithValue(ExtractorConst.CdoField, fieldName);

                try
                {
                    connection.Open();
                    OracleDataReader reader = command.ExecuteReader();
                    int r = 0;
                    while (reader.Read())
                    {
                        // Exptected as 0 or 1 row
                        for (int i = 0; i <= reader.FieldCount - 1; i++)
                        {
                            dbFieldInfo.Add(reader.GetName(i), reader.GetValue(i).ToString().ToUpper());
                        }
                        r++;
                        break;
                    }

                    reader.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }

                return dbFieldInfo;
            }
        }

        /// <summary>
        /// Execute a Query to extract column name from the SQL Statement
        /// </summary>
        /// <param name="tableName">Indicate the table to query</param>
        public static List<string> ExtractColumns(string tableName)
         {
             string queryString = string.Format("SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS WHERE UPPER(TABLE_NAME) = UPPER(:tableName)");
             List<string> columns = new List<string>();
             // The connection string assumes that the Access 
             string connectionString = GetConnectionStringByProvider(ExtractorConst.OracleClient);

             using (var connection =
                 new OracleConnection(connectionString))
             {
                 OracleCommand command = connection.CreateCommand();
                 command.CommandText = queryString;
                 command.Parameters.AddWithValue(ExtractorConst.TableName, tableName);

                 try
                 {
                     connection.Open();
                     OracleDataReader reader = command.ExecuteReader();

                     int r = 0;
                     while (reader.Read())
                     {
                         for (int i = 0; i <= reader.FieldCount - 1; i++)
                         {
                             object obj = reader.GetValue(i);
                             columns.Add(obj.ToString().ToUpper());
                         }
                         r++;
                     }

                     reader.Close();
                 }
                 catch (Exception ex)
                 {
                     Console.WriteLine(ex.Message);
                 }

                 return columns;
             }
         }
    }
}